﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Maticsoft.DBUtility;
using System.Data;
using System.Data.SqlClient;

namespace ElingOA6_Admin_DSJCJ.DAL
{
    /// <summary>
    /// WeekPlan的简要说明
    /// </summary>
    public class WeekPlan
    {
        /// <summary>
        /// 获取第几周
        /// </summary>
        /// <returns>时间列表</returns>
        public DataSet GetTime(string endDate)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append(" select datepart(week,(convert(char(10),dateadd (day,6-(datepart(weekday,dateadd(week,0,'" + endDate + "'))");
            strSql.Append(" + @@datefirst-2)%7-1,dateadd(week,0,'" + endDate + "')),102))  ) as w");
            return DbHelperSQL.Query(strSql.ToString());
        }
        /// <summary>
        /// 获取年度
        /// </summary>
        /// <returns>时间列表</returns>
        public DataSet GetYear(string endDate)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append(" select datepart(YEAR,(convert(char(10),dateadd (day,6-(datepart(weekday,dateadd(week,0,'" + endDate + "'))");
            strSql.Append(" + @@datefirst-2)%7-1,dateadd(week,0,'" + endDate + "')),102))  ) as y");
            return DbHelperSQL.Query(strSql.ToString());
        }
        /// <summary>
        /// 获得这周是几号到几号
        /// </summary>
        /// <param name="gowk">参数</param>
        /// <returns>时间列表</returns>
        public DataSet GetDate(int gowk)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT (a.wk+' 00:00:00') wk, (a.wk1+' 23:59:59') wk1 FROM ");
            strSql.Append("(select convert(char(10),");
            strSql.Append(" dateadd(day,1-(datepart(weekday,dateadd(week," + gowk + ",getdate()))");
            strSql.Append(" + @@datefirst-2)%7-1,dateadd(week," + gowk + ",getdate())),102) as wk ,");
            strSql.Append("convert(char(10),dateadd(day,7-(datepart(weekday,dateadd(week," + gowk + ",getdate()))");
            strSql.Append(" + @@datefirst-2)%7-1,dateadd(week," + gowk + ",getdate())),102)  as wk1");
            strSql.Append("  ) a");
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获得时间的差异
        /// </summary>
        /// <param name="gowk">周的一个参数</param>
        /// <returns>数据列表</returns>
        public DataSet GetTimeDiff(int gowk)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("select datediff(dw,(convert(char(10),dateadd(day,1-(datepart(weekday,dateadd(week," + gowk + ",getdate()))");
            strSql.Append(" + @@datefirst-2)%7-1,dateadd(week," + gowk + ",getdate())),102)),");
            strSql.Append("(convert(char(10),dateadd(day,1-(datepart(weekday,dateadd(week,0,getdate()))");
            strSql.Append("+ @@datefirst-2)%7-1,dateadd(week,0,getdate())),102) )) as d");

            return DbHelperSQL.Query(strSql.ToString());

        }

        /// <summary>
        /// 获得主岗位部门
        /// </summary>
        /// <returns>数据列表</returns>
        public DataSet GetDept()
        {

            StringBuilder strSql = new StringBuilder();

            strSql.Append("select * from  t_HRDept as a left join dbo.t_ICACDeptDoc  as b");
            strSql.Append(" on a.FDeptNo=b.FDeptNo and a.FEntityNo= b.FEntityNo");
            strSql.Append(" where a.FParentDeptNo is null and a.FIsAgency = 'N'");
            strSql.Append(" order by FSeq");
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 增加一条数据
        /// </summary>
        /// <param name="model">Model对象</param>
        /// <returns>插入几个</returns>
        public decimal Add(ElingOA6_Admin_DSJCJ.Model.WeekPlan model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into dbo.t_WeekPlan(");
            strSql.Append("FEmplNo,FEntityNo,FYear,FWeek,FBeginDate,FEndDate,FContent,FInsDate,FUpdDate,FInsIP,FCheckEmpNo,FCheckDate,FCheckIP,FStatus)");
            strSql.Append(" values (");
            strSql.Append("@FEmplNo,@FEntityNo,@FYear,@FWeek,@FBeginDate,@FEndDate,@FContent,@FInsDate,@FUpdDate,@FInsIP,@FCheckEmpNo,@FCheckDate,@FCheckIP,@FStatus)");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
					new SqlParameter("@FEmplNo", SqlDbType.NVarChar,15),
					new SqlParameter("@FEntityNo", SqlDbType.NVarChar,12),
					new SqlParameter("@FYear", SqlDbType.Decimal,5),
					new SqlParameter("@FWeek", SqlDbType.Decimal,5),
					new SqlParameter("@FBeginDate", SqlDbType.DateTime),
					new SqlParameter("@FEndDate", SqlDbType.DateTime),
					new SqlParameter("@FContent", SqlDbType.NVarChar,-1),
					new SqlParameter("@FInsDate", SqlDbType.DateTime),
					new SqlParameter("@FUpdDate", SqlDbType.DateTime),
					new SqlParameter("@FInsIP", SqlDbType.NVarChar,15),
					new SqlParameter("@FCheckEmpNo", SqlDbType.NVarChar,15),
					new SqlParameter("@FCheckDate", SqlDbType.DateTime),
					new SqlParameter("@FCheckIP", SqlDbType.NVarChar,15),
					new SqlParameter("@FStatus", SqlDbType.NVarChar,1)};
            parameters[0].Value = model.FEmplNo;
            parameters[1].Value = model.FEntityNo;
            parameters[2].Value = model.FYear;
            parameters[3].Value = model.FWeek;
            parameters[4].Value = model.FBeginDate;
            parameters[5].Value = model.FEndDate;
            parameters[6].Value = model.FContent;
            parameters[7].Value = model.FInsDate;
            parameters[8].Value = model.FUpdDate;
            parameters[9].Value = model.FInsIP;
            parameters[10].Value = model.FCheckEmpNo;
            parameters[11].Value = model.FCheckDate;
            parameters[12].Value = model.FCheckIP;
            if (model.FStatus == "") {
                parameters[13].Value = DBNull.Value;
            } else {
                parameters[13].Value = model.FStatus;
            }
            

            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToDecimal(obj);
            }
        }
        /// <summary>
        /// 更新一条数据
        /// </summary>
        /// <param name="model">Model对象</param>
        /// <returns>是否更新</returns>
        public bool Update(ElingOA6_Admin_DSJCJ.Model.WeekPlan model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update dbo.t_WeekPlan set ");
            strSql.Append("FContent=@FContent,");
            strSql.Append("FUpdDate=@FUpdDate,");


            strSql.Append("FStatus=@FStatus");

            strSql.Append(" where FWeekPlan=@FWeekPlan");
            SqlParameter[] parameters = {
					new SqlParameter("@FContent", SqlDbType.NVarChar,-1),
					new SqlParameter("@FUpdDate", SqlDbType.DateTime),
					
					
					new SqlParameter("@FStatus", SqlDbType.NVarChar,1),
					new SqlParameter("@FWeekPlan", SqlDbType.Decimal,13)
                };
            parameters[0].Value = model.FContent;
            parameters[1].Value = model.FUpdDate;
          
            if (model.FStatus == "")
            {
                parameters[2].Value = DBNull.Value;
            }
            else
            {
                parameters[2].Value = model.FStatus;
            }

            parameters[3].Value = model.FWeekPlan;
           
           
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        /// <summary>
        /// 审核更新一条数据
        /// </summary>
        /// <param name="model">Model对象</param>
        /// <returns>是否更新</returns>
        public bool UpdateCheck(ElingOA6_Admin_DSJCJ.Model.WeekPlan model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update dbo.t_WeekPlan set ");
            strSql.Append("FCheckDate=@FCheckDate,");
            strSql.Append("FCheckEmpNo=@FCheckEmpNo,");
            strSql.Append("FCheckIP=@FCheckIP,");
            strSql.Append("FStatus=@FStatus");
            strSql.Append(" where FWeekPlan=@FWeekPlan");
            SqlParameter[] parameters = {
					new SqlParameter("@FCheckDate", SqlDbType.DateTime),
					new SqlParameter("@FCheckIP", SqlDbType.NVarChar,15),
					new SqlParameter("@FStatus", SqlDbType.NVarChar,1),
					new SqlParameter("@FWeekPlan", SqlDbType.Decimal,13),
					new SqlParameter("@FCheckEmpNo", SqlDbType.NVarChar,15)};

            parameters[0].Value = model.FCheckDate;
            parameters[1].Value = model.FCheckIP;
            parameters[2].Value = model.FStatus;
            parameters[3].Value = model.FWeekPlan;
            parameters[4].Value = model.FCheckEmpNo;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }




        /// <summary>
        /// 是否存在该记录
        /// </summary>
        /// <param name="fWeek">周</param>
        /// <param name="fYear">年</param>
        /// <param name="fEmplNo">人员编号</param>
        /// <param name="fEntityNo">公司编号</param>
        /// <returns>是否存在该记录</returns>
        public bool Exists(decimal fWeek, decimal fYear, string fEmplNo, string fEntityNo)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from dbo.t_WeekPlan");
            strSql.Append(" where FWeek=@FWeek and FYear=@FYear and FEmplNo=@FEmplNo and FEntityNo=@FEntityNo ");
            SqlParameter[] parameters = {
					new SqlParameter("@FWeek", SqlDbType.Decimal,5),
                     new SqlParameter("@FYear", SqlDbType.Decimal,5),
					new SqlParameter("@FEmplNo", SqlDbType.NVarChar,15),
					new SqlParameter("@FEntityNo", SqlDbType.NVarChar,12)			};
            parameters[0].Value = fWeek;
            parameters[1].Value = fYear;
            parameters[2].Value = fEmplNo;
            parameters[3].Value = fEntityNo;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        /// <param name="fWeek">周</param>
        /// <param name="fYear">年</param>
        /// <param name="fEmplNo">人员编号</param>
        /// <param name="fEntityNo">公司编号</param>
        /// <returns>一个对象实体</returns>
        public ElingOA6_Admin_DSJCJ.Model.WeekPlan GetModel(decimal fWeek, decimal fYear, string fEmplNo, string fEntityNo)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select FWeekPlan,FEmplNo,FEntityNo,FYear,FWeek,FBeginDate,FEndDate,FContent,FInsDate,FUpdDate,FInsIP,FCheckEmpNo,FCheckDate,FCheckIP,FStatus from dbo.t_WeekPlan ");
            strSql.Append(" where FWeek=@FWeek and FYear=@FYear and FEmplNo=@FEmplNo and FEntityNo=@FEntityNo ");
            SqlParameter[] parameters = {
					new SqlParameter("@FWeek", SqlDbType.Decimal,5),
                    new SqlParameter("@FYear", SqlDbType.Decimal,5),
					new SqlParameter("@FEmplNo", SqlDbType.NVarChar,15),
					new SqlParameter("@FEntityNo", SqlDbType.NVarChar,12)
			};
            parameters[0].Value = fWeek;
            parameters[1].Value = fYear;
            parameters[2].Value = fEmplNo;
            parameters[3].Value = fEntityNo;

            ElingOA6_Admin_DSJCJ.Model.WeekPlan model = new ElingOA6_Admin_DSJCJ.Model.WeekPlan();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                return DataRowToModel(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        /// <param name="row">表行</param>
        /// <returns>一个对象实体</returns>
        public ElingOA6_Admin_DSJCJ.Model.WeekPlan DataRowToModel(DataRow row)
        {
            ElingOA6_Admin_DSJCJ.Model.WeekPlan model = new ElingOA6_Admin_DSJCJ.Model.WeekPlan();
            if (row != null)
            {
                if (row["FWeekPlan"] != null && row["FWeekPlan"].ToString() != "")
                {
                    model.FWeekPlan = decimal.Parse(row["FWeekPlan"].ToString());
                }
                if (row["FEmplNo"] != null)
                {
                    model.FEmplNo = row["FEmplNo"].ToString();
                }
                if (row["FEntityNo"] != null)
                {
                    model.FEntityNo = row["FEntityNo"].ToString();
                }
                if (row["FYear"] != null && row["FYear"].ToString() != "")
                {
                    model.FYear = decimal.Parse(row["FYear"].ToString());
                }
                if (row["FWeek"] != null && row["FWeek"].ToString() != "")
                {
                    model.FWeek = decimal.Parse(row["FWeek"].ToString());
                }
                if (row["FBeginDate"] != null && row["FBeginDate"].ToString() != "")
                {
                    model.FBeginDate = DateTime.Parse(row["FBeginDate"].ToString());
                }
                if (row["FEndDate"] != null && row["FEndDate"].ToString() != "")
                {
                    model.FEndDate = DateTime.Parse(row["FEndDate"].ToString());
                }
                if (row["FContent"] != null)
                {
                    model.FContent = row["FContent"].ToString();
                }
                if (row["FInsDate"] != null && row["FInsDate"].ToString() != "")
                {
                    model.FInsDate = DateTime.Parse(row["FInsDate"].ToString());
                }
                if (row["FUpdDate"] != null && row["FUpdDate"].ToString() != "")
                {
                    model.FUpdDate = DateTime.Parse(row["FUpdDate"].ToString());
                }
                if (row["FInsIP"] != null)
                {
                    model.FInsIP = row["FInsIP"].ToString();
                }
                if (row["FCheckEmpNo"] != null)
                {
                    model.FCheckEmpNo = row["FCheckEmpNo"].ToString();
                }
                if (row["FCheckDate"] != null && row["FCheckDate"].ToString() != "")
                {
                    model.FCheckDate = DateTime.Parse(row["FCheckDate"].ToString());
                }
                if (row["FCheckIP"] != null)
                {
                    model.FCheckIP = row["FCheckIP"].ToString();
                }
                if (row["FStatus"] != null)
                {
                    model.FStatus = row["FStatus"].ToString();
                }
            }
            return model;
        }



        /// <summary>
        /// 得到人员编号和人员名称
        /// </summary>
        /// <param name="deptNo">部门编号</param>
        /// <returns>数据列表</returns>
        public DataSet GetEmpNo(string deptNo)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("select FEmplNo,FEmpName from v_HRDeptEmpl");
            if (deptNo.Trim() != "")
            {
                strSql.Append(" where FDeptNo=" + deptNo);
            }

            return DbHelperSQL.Query(strSql.ToString());
        
        }
        /// <summary>
        /// 得到人员编号和人员名称
        /// </summary>
        /// <param name="deptNoList">部门编号列表</param>
        /// <returns>数据列表</returns>
        public DataSet GetEmpNoByDeptNoList(List<string> deptNoList)
        {
            StringBuilder strSql = new StringBuilder();
            string deptNos = "";
            strSql.Append("select FEmplNo,FEmpName from v_HRDeptEmpl");
            strSql.Append(" WHERE FIsMainPosition='Y' ");
            if (deptNoList.Count > 0)
            {
                foreach (string deptNo in deptNoList)
                {
                    deptNos += "'" + deptNo + "',";
                }
                if (deptNos != "")
                {
                    deptNos = deptNos.Substring(0, deptNos.LastIndexOf(','));
                }
                strSql.Append(" AND FDeptNo IN (" + deptNos + ")");
            }
            strSql.Append(" ORDER BY FSeq,FDutyNo");
            return DbHelperSQL.Query(strSql.ToString());

        }

        /// <summary>
        /// 没有审核人员的信息(FEndDate>=2013年)
        /// </summary>
        /// <param name="empNo">人员编号</param>
        /// <param name="entity">公司编号</param>
        /// <returns>数据列表</returns>
        public DataSet GetNoAuIn(string empNo, string entity)
        {

            StringBuilder strSql = new StringBuilder();

            strSql.Append("select * from dbo.t_WeekPlan");
            strSql.Append(" where FStatus is null and FEmplNo='" + empNo + "' and FEntityNo='" + entity + "' AND Year(FEndDate)>=2013");
            strSql.Append(" order by FWeekPlan");

            return DbHelperSQL.Query(strSql.ToString());

        }


        /// <summary>
        /// 得到需要考核人员数目(FEndDate>=2013年)
        /// </summary>
        /// <param name="empNo">人员编号</param>
        /// <param name="entity">公司编号</param>
        /// <returns>数据列表</returns>
        public DataSet GetCou(string empNo, string entity)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(*) as cou from ");
            strSql.Append(" (select DISTINCT  FEmplNo  from dbo.t_WeekPlan ");
            strSql.Append(" where FStatus is null and  FEmplNo= '" + empNo + "' and FEntityNo='" + entity + "' AND Year(FEndDate)>=2013 ) t");
            return DbHelperSQL.Query(strSql.ToString());
        }



        /// <summary>
        /// 得到非工作日的天数
        /// </summary>
        /// <param name="startDate">开始时间</param>
        /// <param name="endDate">结束时间</param>
        /// <returns>得到几条非工作日的天数</returns>
        public decimal GetNoWorkDay(string startDate, string endDate)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(*) cou from t_WorkCalendar ");
            strSql.Append(" where convert(char(10),FWorkDate,102) >= @FWorkDateStart ");
            strSql.Append(" and convert(char(10),FWorkDate,102) <= @FWorkDateEnd ");
            strSql.Append(" and Fdeftype <> '1' ");
            SqlParameter[] parameters = {
					new SqlParameter("@FWorkDateStart", SqlDbType.DateTime),
                    new SqlParameter("@FWorkDateEnd", SqlDbType.DateTime)
			};
            parameters[0].Value = startDate;
            parameters[1].Value = endDate;


            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToDecimal(obj);
            }
        }


        /// <summary>
        ///  周工作计划未按时上报-未及时上报
        /// </summary>
        /// <param name="startDate">开始日期</param>
        /// <param name="endDate">结束日期</param>
        /// <returns>数据列表</returns>
        public DataSet GetNoWeekPlan(string startDate, string endDate)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from ");
            strSql.Append(" (SELECT b.FEmplNo,b.deptName,b.FEmpName,b.FDeptNo,b.FDutyNo,b.FSeq,b.FWorkDate,b.FLeaveDate FROM t_HRDept as a left join v_HRDeptEmpl as b ");
            strSql.Append(" on a.FDeptNo=b.FDeptNo and a.FEntityNo=b.FEntityNo ");
            strSql.Append(" where FIsMainPosition='Y' and a.FIsAgency='N' and b.FIsCheck='Y'  and FEmpStatus <> 99) AS a");
            strSql.Append(" where a.FEmplNo not in (select FEmplNo from dbo.t_WeekPlan where DATEDIFF(DAY,FBeginDate,@FBeginDate1)>=0 ");
            strSql.Append(@"  and DATEDIFF(DAY,FEndDate,@FEndDate1)<=0) and ((FWorkDate IS NULL AND FLeaveDate IS NULL) 
OR (FWorkDate IS NOT NULL AND DATEDIFF(DAY,FWorkDate,@FEndDate3)>0 AND  FLeaveDate IS NULL)
OR (FLeaveDate IS NOT NULL AND DATEDIFF(DAY,FLeaveDate,@FBeginDate3)<0 AND FWorkDate IS NULL )
OR (FWorkDate IS NOT NULL AND FLeaveDate IS NOT NULL AND DATEDIFF(DAY,FWorkDate,@FEndDate4)>0  AND DATEDIFF(DAY,FLeaveDate,@FBeginDate4)<0))");
            strSql.Append("  union all");
            strSql.Append(" select * from (SELECT b.FEmplNo,b.deptName,b.FEmpName,b.FDeptNo,b.FDutyNo,b.FSeq,b.FWorkDate,b.FLeaveDate FROM t_HRDept as a left join v_HRDeptEmpl as b  ");
            strSql.Append("  on a.FDeptNo=b.FDeptNo and a.FEntityNo=b.FEntityNo ");
            strSql.Append("  where FIsMainPosition='Y' and a.FIsAgency='N' and b.FIsCheck='Y'  and FEmpStatus <> 99) AS a ");
            strSql.Append(" where a.FEmplNo in (select FEmplNo from dbo.t_WeekPlan where DATEDIFF(DAY,FBeginDate,@FBeginDate2)>=0 ");
            strSql.Append(@" and DATEDIFF(DAY,FEndDate,@FEndDate2)<=0 and DATEDIFF(DAY,FBeginDate,FInsDate) > 0) and ((FWorkDate IS NULL AND FLeaveDate IS NULL) 
OR (FWorkDate IS NOT NULL AND DATEDIFF(DAY,FWorkDate,@FEndDate5)>0 AND  FLeaveDate IS NULL)
OR (FLeaveDate IS NOT NULL AND DATEDIFF(DAY,FLeaveDate,@FBeginDate5)<0 AND FWorkDate IS NULL )
OR (FWorkDate IS NOT NULL AND FLeaveDate IS NOT NULL AND DATEDIFF(DAY,FWorkDate,@FEndDate6)>0  AND DATEDIFF(DAY,FLeaveDate,@FBeginDate6)<0))");
            strSql.Append("  order by FDeptNo,FSeq,FDutyNo");
            SqlParameter[] parameters = {
					new SqlParameter("@FBeginDate1", SqlDbType.DateTime),
                    new SqlParameter("@FEndDate1", SqlDbType.DateTime),
                    new SqlParameter("@FBeginDate2", SqlDbType.DateTime),
                    new SqlParameter("@FEndDate2", SqlDbType.DateTime),
                    new SqlParameter("@FBeginDate3", SqlDbType.DateTime),
                    new SqlParameter("@FEndDate3", SqlDbType.DateTime),
                    new SqlParameter("@FBeginDate4", SqlDbType.DateTime),
                    new SqlParameter("@FEndDate4", SqlDbType.DateTime),
                    new SqlParameter("@FBeginDate5", SqlDbType.DateTime),
                    new SqlParameter("@FEndDate5", SqlDbType.DateTime),
                    new SqlParameter("@FBeginDate6", SqlDbType.DateTime),
                    new SqlParameter("@FEndDate6", SqlDbType.DateTime)
			};
            parameters[0].Value = startDate;
            parameters[1].Value = endDate;
            parameters[2].Value = startDate;
            parameters[3].Value = endDate;
            parameters[4].Value = startDate;
            parameters[5].Value = endDate;
            parameters[6].Value = startDate;
            parameters[7].Value = endDate;
            parameters[8].Value = startDate;
            parameters[9].Value = endDate;
            parameters[10].Value = startDate;
            parameters[11].Value = endDate;


            return DbHelperSQL.Query(strSql.ToString(), parameters);
        
        }


        /// <summary>
        /// 工作计划未按时审核
        /// </summary>
        /// <param name="startDate">开始日期</param>
        /// <param name="endDate">结束日期</param>
        /// <returns>数据列表</returns>
        public DataSet GetNoCheck(string startDate, string endDate)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select *,'1' AS TYPE from  ");
            strSql.Append(" (SELECT b.FEmplNo,b.deptName,b.FEmpName,b.FDeptNo,b.FDutyNo,b.FSeq,b.FWorkDate,b.FLeaveDate  FROM t_HRDept as a left join v_HRDeptEmpl as b ");
            strSql.Append(" on a.FDeptNo=b.FDeptNo and a.FEntityNo=b.FEntityNo ");
            strSql.Append(" where FIsMainPosition='Y' and a.FIsAgency='N' and b.FIsCheck='Y'  and FEmpStatus <> 99 ) AS a");
            strSql.Append(" where a.FEmplNo not in (select FEmplNo from dbo.t_WeekPlan where DATEDIFF(DAY,FBeginDate,@FBeginDate1)>=0");
            strSql.Append(@" and DATEDIFF(DAY,FEndDate,@FEndDate1)<=0  ) and ((FWorkDate IS NULL AND FLeaveDate IS NULL) 
OR (FWorkDate IS NOT NULL AND DATEDIFF(DAY,FWorkDate,@FEndDate3)>0 AND  FLeaveDate IS NULL)
OR (FLeaveDate IS NOT NULL AND DATEDIFF(DAY,FLeaveDate,@FBeginDate3)<0 AND FWorkDate IS NULL )
OR (FWorkDate IS NOT NULL AND FLeaveDate IS NOT NULL AND DATEDIFF(DAY,FWorkDate,@FEndDate4)>0  AND DATEDIFF(DAY,FLeaveDate,@FBeginDate4)<0))");
            strSql.Append(" union all");
            strSql.Append(" select *,'2' AS TYPE from (SELECT b.FEmplNo,b.deptName,b.FEmpName,b.FDeptNo,b.FDutyNo,b.FSeq ,b.FWorkDate,b.FLeaveDate ");
            strSql.Append(" FROM t_HRDept as a left join v_HRDeptEmpl as b  ");
            strSql.Append(" on a.FDeptNo=b.FDeptNo and a.FEntityNo=b.FEntityNo ");
            strSql.Append(" where FIsMainPosition='Y' and a.FIsAgency='N' and b.FIsCheck='Y'  and FEmpStatus <> 99) AS a");
            strSql.Append(" where a.FEmplNo in (select FEmplNo from dbo.t_WeekPlan where DATEDIFF(DAY,FBeginDate,@FBeginDate2)>=0 ");
            strSql.Append(" and DATEDIFF(DAY,FEndDate,@FEndDate2)<=0 ");
            strSql.Append(@" and (DATEDIFF(day,[dbo].[fn_getWeekFirstDay](FBeginDate)+1,FCheckDate)>0 or FCheckDate is null) ) and ((FWorkDate IS NULL AND FLeaveDate IS NULL) 
OR (FWorkDate IS NOT NULL AND DATEDIFF(DAY,FWorkDate,@FEndDate5)>0 AND  FLeaveDate IS NULL)
OR (FLeaveDate IS NOT NULL AND DATEDIFF(DAY,FLeaveDate,@FBeginDate5)<0 AND FWorkDate IS NULL )
OR (FWorkDate IS NOT NULL AND FLeaveDate IS NOT NULL AND DATEDIFF(DAY,FWorkDate,@FEndDate6)>0  AND DATEDIFF(DAY,FLeaveDate,@FBeginDate6)<0))");
            strSql.Append(" order by FDeptNo,FSeq,FDutyNo");
            SqlParameter[] parameters = {
					new SqlParameter("@FBeginDate1", SqlDbType.DateTime),
                    new SqlParameter("@FEndDate1", SqlDbType.DateTime),
                    new SqlParameter("@FBeginDate2", SqlDbType.DateTime),
                    new SqlParameter("@FEndDate2", SqlDbType.DateTime),
                    new SqlParameter("@FBeginDate3", SqlDbType.DateTime),
                    new SqlParameter("@FEndDate3", SqlDbType.DateTime),
                    new SqlParameter("@FBeginDate4", SqlDbType.DateTime),
                    new SqlParameter("@FEndDate4", SqlDbType.DateTime),
                    new SqlParameter("@FBeginDate5", SqlDbType.DateTime),
                    new SqlParameter("@FEndDate5", SqlDbType.DateTime),
                    new SqlParameter("@FBeginDate6", SqlDbType.DateTime),
                    new SqlParameter("@FEndDate6", SqlDbType.DateTime)
			};
            parameters[0].Value = startDate;
            parameters[1].Value = endDate;
            parameters[2].Value = startDate;
            parameters[3].Value = endDate;
            parameters[4].Value = startDate;
            parameters[5].Value = endDate;
            parameters[6].Value = startDate;
            parameters[7].Value = endDate;
            parameters[8].Value = startDate;
            parameters[9].Value = endDate;
            parameters[10].Value = startDate;
            parameters[11].Value = endDate;

            return DbHelperSQL.Query(strSql.ToString(), parameters);

        }


        /// <summary>
        /// 日志未完整登记
        /// </summary>
        /// <param name="startDate">开始日期</param>
        /// <param name="endDate">结束日期</param>
        /// <returns>数据列表</returns>
        public DataSet GetNoReg(string startDate, string endDate)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append(" select B.* from ( select A.*,FSeq from(");
            strSql.Append(" select *,'1' AS TYPE from ");
            strSql.Append(" (SELECT b.FEmplNo,b.deptName,b.FEmpName,b.FDeptNo,b.FWorkDate,b.FLeaveDate  ");
            strSql.Append(" FROM t_HRDept as a left join v_HRDeptEmpl as b ");
            strSql.Append(" on a.FDeptNo=b.FDeptNo and a.FEntityNo=b.FEntityNo");
            strSql.Append(" where FIsMainPosition='Y' and a.FIsAgency='N' and b.FIsCheck='Y'  and FEmpStatus <> 99) AS a");
            strSql.Append(" where a.FEmplNo not in (select FEmplNo from dbo.t_Daily ");
            strSql.Append("  where DATEDIFF(DAY,FDate,@FEndDate1)>=0");
            strSql.Append(" and DATEDIFF(DAY,FDate,@FBeginDate1)<=0  ");
            strSql.Append(" AND ( SELECT    COUNT(*) FROM  dbo.t_WorkCalendar WHERE  FWorkDate = dbo.t_Daily.FDate AND Fdeftype = 2) = 0");//排除休息日
            strSql.Append(" GROUP BY FEmplNo HAVING COUNT(FEmplNo)=(");
            strSql.Append(" SELECT COUNT(*) FROM  dbo.t_WorkCalendar");
            strSql.Append(" where DATEDIFF(DAY,FWorkDate,@FEndDate3)>=0   and DATEDIFF(DAY,FWorkDate,@FBeginDate3)<=0 ");
            strSql.Append(@" AND Fdeftype=1)) and ((FWorkDate IS NULL AND FLeaveDate IS NULL) 
OR (FWorkDate IS NOT NULL AND DATEDIFF(DAY,FWorkDate,@FEndDate4)>0 AND  FLeaveDate IS NULL)
OR (FLeaveDate IS NOT NULL AND DATEDIFF(DAY,FLeaveDate,@FBeginDate4)<0 AND FWorkDate IS NULL )
OR (FWorkDate IS NOT NULL AND FLeaveDate IS NOT NULL AND DATEDIFF(DAY,FWorkDate,@FEndDate5)>0  AND DATEDIFF(DAY,FLeaveDate,@FBeginDate5)<0))");           
            strSql.Append(" union all");
            strSql.Append(" select *,'2' AS TYPE from (SELECT b.FEmplNo,b.deptName,b.FEmpName,b.FDeptNo,b.FWorkDate,b.FLeaveDate ");
            strSql.Append(" FROM t_HRDept as a left join v_HRDeptEmpl as b ");
            strSql.Append(" on a.FDeptNo=b.FDeptNo and a.FEntityNo=b.FEntityNo ");
            strSql.Append(" where FIsMainPosition='Y' and a.FIsAgency='N' and b.FIsCheck='Y' and FEmpStatus <> 99) AS a ");
            strSql.Append(" where a.FEmplNo in (select FEmplNo from dbo.t_Daily");
            strSql.Append(" where DATEDIFF(DAY,FDate,@FEndDate2)>=0");
            strSql.Append(" and DATEDIFF(DAY,FDate,@FBeginDate2)<=0  ");
            strSql.Append(" AND ( SELECT  COUNT(*) FROM  dbo.t_WorkCalendar WHERE FWorkDate = dbo.t_Daily.FDate AND Fdeftype = 2) = 0");//排除休息日
            strSql.Append(@" AND FContent IS NULL)  and ((FWorkDate IS NULL AND FLeaveDate IS NULL) 
OR (FWorkDate IS NOT NULL AND DATEDIFF(DAY,FWorkDate,@FEndDate6)>0 AND  FLeaveDate IS NULL)
OR (FLeaveDate IS NOT NULL AND DATEDIFF(DAY,FLeaveDate,@FBeginDate6)<0 AND FWorkDate IS NULL )
OR (FWorkDate IS NOT NULL AND FLeaveDate IS NOT NULL AND DATEDIFF(DAY,FWorkDate,@FEndDate7)>0  AND DATEDIFF(DAY,FLeaveDate,@FBeginDate7)<0))");
            strSql.Append(" ) AS A inner join t_HRDept ON t_HRDept.FDeptNo = A.FDeptNo");
            strSql.Append(" ) AS B inner join t_HREmpl");
            strSql.Append(" ON t_HREmpl.FEmplNo =B.FEmplNo ORDER BY B.FSeq,t_HREmpl.FSeq,t_HREmpl.FDutyNo");

            SqlParameter[] parameters = {
					new SqlParameter("@FBeginDate1", SqlDbType.DateTime),
                    new SqlParameter("@FEndDate1", SqlDbType.DateTime),
                    new SqlParameter("@FBeginDate2", SqlDbType.DateTime),
                    new SqlParameter("@FEndDate2", SqlDbType.DateTime),
                    new SqlParameter("@FBeginDate3", SqlDbType.DateTime),
                    new SqlParameter("@FEndDate3", SqlDbType.DateTime),
                    new SqlParameter("@FBeginDate4", SqlDbType.DateTime),
                    new SqlParameter("@FEndDate4", SqlDbType.DateTime),
                    new SqlParameter("@FBeginDate5", SqlDbType.DateTime),
                    new SqlParameter("@FEndDate5", SqlDbType.DateTime),
                    new SqlParameter("@FBeginDate6", SqlDbType.DateTime),
                    new SqlParameter("@FEndDate6", SqlDbType.DateTime),
                    new SqlParameter("@FBeginDate7", SqlDbType.DateTime),
                    new SqlParameter("@FEndDate7", SqlDbType.DateTime)
			};
            parameters[0].Value = startDate;
            parameters[1].Value = endDate;
            parameters[2].Value = startDate;
            parameters[3].Value = endDate;
            parameters[4].Value = startDate;
            parameters[5].Value = endDate;
            parameters[6].Value = startDate;
            parameters[7].Value = endDate;
            parameters[8].Value = startDate;
            parameters[9].Value = endDate;
            parameters[10].Value = startDate;
            parameters[11].Value = endDate;
            parameters[12].Value = startDate;
            parameters[13].Value = endDate;


            return DbHelperSQL.Query(strSql.ToString(), parameters);

        }



        /// <summary>
        /// 需要考核的部门（不是外派机构）
        /// </summary>
        /// <returns>数据列表</returns>
        public DataSet GetCheckDept()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from t_HRDept");
            strSql.Append(" where FIsAgency='N' and FParentDeptNo is null");
            strSql.Append(" order by FSeq");
            return DbHelperSQL.Query(strSql.ToString());

        }


        /// <summary>
        /// 需要考核人员
        /// </summary>
        /// <param name="fDeptNO">部门编号</param>
        /// <returns>数据列表</returns>
        public DataSet GetChechEmp(string fDeptNO) {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from v_HRDeptEmpl");
            strSql.Append(" where FDeptNo=@FDeptNo and FIsMainPosition='Y' and FIsCheck ='Y' and FEmpStatus <> 99");
            strSql.Append(" order by FSeq,FDutyNo");

            SqlParameter[] parameters = {
					new SqlParameter("@FDeptNo", SqlDbType.NVarChar,15),
			};
            parameters[0].Value = fDeptNO;

            return DbHelperSQL.Query(strSql.ToString(), parameters);

        }

        /// <summary>
        /// 得到工作人员的名字
        /// </summary>
        /// <param name="empNo">人员编号</param>
        /// <returns>字符</returns>
        public string GetempName(string empNo)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select FEmpName from v_HRDeptEmpl");
            strSql.Append(" where FEmplNo=@FEmplNo");
            strSql.Append(" order by FSeq");

            SqlParameter[] parameters = {
					new SqlParameter("@FEmplNo", SqlDbType.NVarChar,15),
			};
            parameters[0].Value = empNo;

            return DbHelperSQL.GetString(strSql.ToString(), parameters);

        }

        /// <summary>
        /// 查看页面的显示文字
        /// </summary>
        /// <param name="beginDate">开始日期</param>
        /// <param name="empNo">人员编号</param>
        /// <returns>数据列表</returns>
        public DataSet GetPlanText(string beginDate, string empNo)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from dbo.t_WeekPlan");
            strSql.Append(" where FBeginDate=@FBeginDate and FEmplNo=@FEmplNo");

            SqlParameter[] parameters = {
                    new SqlParameter("@FBeginDate", SqlDbType.DateTime),
					new SqlParameter("@FEmplNo", SqlDbType.NVarChar,15)
			};
            parameters[0].Value = beginDate;
            parameters[1].Value = empNo;

            return DbHelperSQL.Query(strSql.ToString(), parameters);

        }

        /// <summary>
        /// 获得所有科室所有未及时上报和未及时审核的计数(最近两年 大于0)(2013年之后)
        /// </summary>
        /// <returns>数据列表</returns>
        public DataSet GetAllData(string entityNo)
        {
            //StringBuilder strSql = new StringBuilder();
            //strSql.Append("SELECT  * FROM (");
            //strSql.Append("select a.FDeptNo,a.FYear,FBeginDate,FEndDate,a.FWeek,FDeptName,[count]-ISNULL(count2,0)");
            //strSql.Append(" as counts,ISNULL(count3,0) as count3,[count]-ISNULL(count2,0)+ isnull(count3,0) as sumcount from (");
            //strSql.Append(" select * from (");
            //strSql.Append(" select COUNT(*) as [count],FDeptNo,FDeptName,'1' as type1 from (");
            //strSql.Append(" SELECT a.FDeptName, b.FEmplNo,b.deptName,b.FEmpName,b.FDeptNo ");
            //strSql.Append(" FROM t_HRDept as a left join v_HRDeptEmpl as b ");
            //strSql.Append(" on a.FDeptNo=b.FDeptNo and a.FEntityNo=b.FEntityNo ");
            //strSql.Append(" where FIsMainPosition='Y' and a.FIsAgency='N' and b.FIsCheck='Y' and FEmpStatus <> 99)");
            //strSql.Append(" as a group by FDeptNo,FDeptName) as c");
            //strSql.Append(" inner join(");
            //strSql.Append(" select FYear,FWeek,FBeginDate,FEndDate,'1' as type from dbo.t_WeekPlan");
            //strSql.Append(" group by FYear,FWeek,FBeginDate,FEndDate) as b on c.type1=b.type ) as a ");
            //strSql.Append(" left join (");
            //strSql.Append(" select FWeek,FDeptNo,FYear,COUNT(*) as count2 from dbo.t_WeekPlan a ");            
            //strSql.Append(" left join v_HRDeptEmpl b on a.FEmplNo=b.FEmplNo");
            //strSql.Append(" where DATEDIFF(day,FBeginDate,FInsDate) < 0 ");//本周计划在上周日之前（包括周日）（就是下周计划在本周日之前完成）(已按时上报人数)
            //strSql.Append(" group by a.FYear,a.FWeek,b.FDeptNo) b ");
            //strSql.Append(" on a.FDeptNo=b.FDeptNo and a.FYear=b.FYear and a.FWeek=b.FWeek");
            //strSql.Append(" left join (");
            //strSql.Append(" select FWeek,FDeptNo,FYear,COUNT(*) as count3 from dbo.t_WeekPlan a");
            //strSql.Append(" left join v_HRDeptEmpl b on a.FEmplNo=b.FEmplNo");
            //strSql.Append(" where DATEDIFF(day,[dbo].[fn_getWeekFirstDay](a.FBeginDate)+1,a.FCheckDate)>0 OR a.FCheckDate IS null ");//本周计划在本周一之前（包括本周一）审核(未按时审核人数)
            //strSql.Append(" group by a.FYear,a.FWeek,b.FDeptNo) as c ");
            //strSql.Append(" on a.FDeptNo=c.FDeptNo and a.FYear=c.FYear and a.FWeek=c.FWeek");
            //strSql.Append(" ) e ");
            //strSql.Append(" WHERE DATEDIFF(day,[dbo].[fn_getWeekFirstDay](e.FBeginDate),GETDATE()) > 0 AND (e.counts>=0 and e.count3>=0) and sumcount>0 AND e.FYear>=2013 AND ( e.FYear = @Year1 OR e.FYear=@Year2)");
            //strSql.Append(" order by e.FYear desc , e.FEndDate desc ,e.FWeek desc, e.FDeptNo");

            //SqlParameter[] parameters = {
            //        new SqlParameter("@Year1", SqlDbType.Decimal),
            //        new SqlParameter("@Year2", SqlDbType.Decimal)
            //};

            //parameters[0].Value = year - 1;
            //parameters[1].Value = year;
            StringBuilder strSql = new StringBuilder();

            strSql.Append("SELECT * FROM [fn_WeekPlanSummary](@EntityNo)");
            strSql.Append(@" ORDER BY  FYear DESC ,
                FWeek DESC ,
                FSeq ,
                FDeptNo");
            SqlParameter[] parameters = {
                    new SqlParameter("@EntityNo", SqlDbType.VarChar)
            };
            parameters[0].Value = entityNo;
            return DbHelperSQL.Query(strSql.ToString(), parameters);
        }

    }
}
